• Notebook Author: Tee Jun Yun
  • Refer to Technical Documentation
In [93]:
import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import seaborn as sb
import numpy as np
from enum import Enum
In [94]:
cnx = mysql.connector.connect(user='user', password='root',
                              host='127.0.0.1',
                              database='data_analytics')
cursor = cnx.cursor(dictionary = True)

Exploratory Data Analysis for Maximizing Stock Market Returns and Managing Risk¶

The primary goal of this project is to provide a comprehensive exploration of the data, enabling us to identify patterns, relationships, and key factors crucial for making informed investment decisions. By delving deep into the historical trends and financial metrics, we aim to not only maximize returns but also effectively manage risk within the stock market landscape.

1 Project Overview¶

1.1 Objective¶

The core objective of this project is to perform Exploratory Data Analysis (EDA) to gain a profound understanding of the available dataset. Through this analysis, we intend to uncover significant insights that could potentially guide strategic investment decisions.

1.2 Methodology¶

Our approach involves meticulous examination and visualization of the past 10 years of historical stock price data alongside various financial indicators. By employing statistical analysis, visualization techniques, and correlation studies, we seek to unveil hidden patterns and relationships among different variables.

2 Data Analysis of Stock Price¶

In this section, we will first focus on analyzing the Stock Price data. The aim is to unveil an overarching trend within the market and discern the nuances of price action. This analysis serves as a foundational exploration, offering insights into the broader movements and behaviors within the stock market.

2.1 Retrieve Stock Prices and Industry¶

In [95]:
sql_query = '''
    SELECT DISTINCT
        StockPrices.date, 
        StockPrices.stock_id, 
        StockPrices.open, 
        StockPrices.close, 
        StockPrices.high, 
        StockPrices.low, 
        StockPrices.volume, 
        StocksIndustries.industry_id 
    FROM 
        StocksIndustries 
    INNER JOIN StockPrices USING(stock_id)
'''
cursor.execute(sql_query)
result = cursor.fetchall()
df = pd.DataFrame(result)
df = df[::-1].reset_index(drop=True)

2.1.1 Aggregate the Returns and Cumulative Return Values for each stock¶

In [96]:
df['return'] = df.groupby(['stock_id', 'industry_id'])['close'].pct_change()
df['cum_returns'] = df.groupby(['stock_id', 'industry_id'])['return'].transform(lambda x: (1 + x).cumprod())
df
Out[96]:
date stock_id open close high low volume industry_id return cum_returns
0 2013-11-06 WELL 63.00 63.27 63.560 62.8500 1411071 Utilities NaN NaN
1 2013-11-07 WELL 62.71 61.43 62.830 61.3700 1526318 Utilities -0.029082 0.970918
2 2013-11-08 WELL 61.00 60.41 61.000 59.5000 2373307 Utilities -0.016604 0.954797
3 2013-11-11 WELL 60.41 60.08 60.840 59.9200 1692912 Utilities -0.005463 0.949581
4 2013-11-12 WELL 60.00 59.64 60.190 59.3400 1683871 Utilities -0.007324 0.942627
... ... ... ... ... ... ... ... ... ... ...
138375 2023-10-30 CMCSA 39.96 40.45 40.620 39.9400 18202260 Communication Services 0.020434 1.692115
138376 2023-10-31 CMCSA 40.70 41.29 41.340 40.6688 18860960 Communication Services 0.020766 1.727254
138377 2023-11-01 CMCSA 41.37 41.82 42.135 41.3400 19505410 Communication Services 0.012836 1.749425
138378 2023-11-02 CMCSA 41.86 42.50 42.580 41.8600 16755390 Communication Services 0.016260 1.777871
138379 2023-11-03 CMCSA 42.86 43.18 43.420 42.8201 19005630 Communication Services 0.016000 1.806317

138380 rows × 10 columns

2.2 Plot Close and Cumulative Returns¶

2.2.1 Close Price¶

In [97]:
groups = df.groupby('industry_id')
industry_ids = df['industry_id'].dropna().unique()
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)
# Iterate over each industry_id and plot close values
for i, industry_id in enumerate(industry_ids):
    ax = axes[i]
    industry_data = groups.get_group(industry_id)
    # Iterate over each stock in the industry and plot the close values
    for stock_id, stock_data in industry_data.groupby('stock_id'):
        ax.plot(stock_data['date'], stock_data['close'], label=stock_id)

    ax.set_title(industry_id)
    ax.set_xlabel('Date')
    ax.set_ylabel('Close Price')
    ax.legend()
In [98]:
# Plot Correlation Coefficient of Closing Price
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)

for i, industry_id in enumerate(industry_ids):
    ax = axes[i]
    ax.set_title(industry_id)
    industry_data = groups.get_group(industry_id)
    # Pivot the DataFrame to have stock_id as columns and closing prices as values
    close_df = industry_data.pivot(index='date', columns='stock_id', values='close')
    pearsoncorr = close_df.corr(method ='pearson')
    sb.heatmap(pearsoncorr, ax = ax, xticklabels=pearsoncorr.columns, yticklabels=pearsoncorr.columns, cmap='RdBu_r', annot=True, linewidth=0.5)

2.2.2 Cumulative Returns¶

In [99]:
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)
# Iterate over each industry_id and plot cumulative returns values
for i, industry_id in enumerate(industry_ids):
    ax = axes[i]
    industry_data = groups.get_group(industry_id)
    # Iterate over each stock in the industry and plot the cumulative returns values
    for stock_id, stock_data in industry_data.groupby('stock_id'):
        ax.plot(stock_data['date'], stock_data['cum_returns'], label=stock_id)

    ax.set_title(industry_id)
    ax.set_xlabel('Date')
    ax.set_ylabel('Cumulative Returns Price')
    ax.legend()
In [100]:
fig, axes = plt.subplots(nrows=len(industry_ids), ncols=1, figsize=(10, 5*len(industry_ids)), sharex=True)

for i, industry_id in enumerate(industry_ids):
    ax = axes[i]
    ax.set_title(industry_id)
    industry_data = groups.get_group(industry_id)
    close_df = industry_data.pivot(index='date', columns='stock_id', values='cum_returns')
    pearsoncorr = close_df.corr(method ='pearson')
    sb.heatmap(pearsoncorr, ax = ax, xticklabels=pearsoncorr.columns, yticklabels=pearsoncorr.columns, cmap='RdBu_r', annot=True, linewidth=0.5)

2.3 Analysis on Historical Price Data¶

Upon delving into the historical stock prices, our analysis pinpointed outliers within the specific industries. While the majority of stocks within these industries exhibit a tendency to correlate, notable exceptions emerged.

Particularly, within the Industrials, Financials, and Energy sectors, the closing prices and cumulative returns displayed a contrary trend for some stocks.

Conversely, the Information Technology sector displayed a consistent directional movement, yet the cumulative returns graph highlighted NVDA stock's exponential growth trajectory.

3 Data Analysis of Financial Metrics¶

In this section, our concentration will be on dissecting NVDA stock's exponential growth trajectory through an examination of its financial metrics. AAPL, on the other hand, will be utilized as the benchmark stock for comparative analysis. By juxtaposing the financial indicators of these two stocks, we aim to dissect and comprehend the factors contributing to NVDA's exceptional growth, utilizing AAPL as a reference point for a comprehensive comparative assessment.

3.1 Retrieve Financial Metrics for NVDA and AAPL¶

In [101]:
sql_query = '''
    SELECT DISTINCT
        *
    FROM 
        StockFinancialData 
    WHERE stock_id = 'AAPL' OR stock_id = 'NVDA';
'''
cursor.execute(sql_query)
result = cursor.fetchall()
df = pd.DataFrame(result)

Context: Due to differing year-ending financial statements between AAPL and NVDA—AAPL's year-end being September 2023 and NVDA's being January 2023—adjustments are necessary in aggregating the years. Specifically, the aggregation aims to exclude AAPL's year 0 and NVDA's year 10 from the analysis, aligning the data effectively to accommodate these dissimilar year-end reporting periods.

In [102]:
df.loc[df['stock_id'] == 'AAPL', 'year'] -= 1
df = df[df['year'] != -1]
df = df[df['year'] != 9]
df
Out[102]:
id namespace fact value units start_date end_date stock_id year
19 20 us-gaap AccountsPayableCurrent 64115000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 0
20 21 us-gaap AccountsReceivableNetCurrent 28184000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 0
21 22 us-gaap AssetsCurrent 135405000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 0
22 23 us-gaap AssetsNoncurrent 217350000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 0
23 24 us-gaap CashAndCashEquivalentsAtCarryingValue 23646000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 0
... ... ... ... ... ... ... ... ... ...
350 768 us-gaap CostOfGoodsAndServicesSold 2082030000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 8
351 769 us-gaap OtherLiabilitiesCurrent 602807000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 8
352 770 us-gaap OtherAssetsCurrent 70174000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 8
353 771 us-gaap LongTermDebtCurrent 0.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 8
354 772 us-gaap LongTermDebtNoncurrent 1384342000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 8

336 rows × 9 columns

In [103]:
convert_index_to_years = {
    0: 2022,
    1: 2021,
    2: 2020,
    3: 2019,
    4: 2018,
    5: 2017,
    6: 2016,
    7: 2015,
    8: 2014
}

df['year'] = df['year'].replace(convert_index_to_years)
df
Out[103]:
id namespace fact value units start_date end_date stock_id year
19 20 us-gaap AccountsPayableCurrent 64115000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 2022
20 21 us-gaap AccountsReceivableNetCurrent 28184000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 2022
21 22 us-gaap AssetsCurrent 135405000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 2022
22 23 us-gaap AssetsNoncurrent 217350000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 2022
23 24 us-gaap CashAndCashEquivalentsAtCarryingValue 23646000000.0000 USD 2022-09-24 00:00:00 2022-09-24 00:00:00 AAPL 2022
... ... ... ... ... ... ... ... ... ...
350 768 us-gaap CostOfGoodsAndServicesSold 2082030000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 2014
351 769 us-gaap OtherLiabilitiesCurrent 602807000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 2014
352 770 us-gaap OtherAssetsCurrent 70174000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 2014
353 771 us-gaap LongTermDebtCurrent 0.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 2014
354 772 us-gaap LongTermDebtNoncurrent 1384342000.0000 USD 2015-01-29 00:00:00 2015-01-29 00:00:00 NVDA 2014

336 rows × 9 columns

In [104]:
class Facts(Enum):
    TOTAL_NET_SALES = 'RevenueFromContractWithCustomerExcludingAssessedTax'
    GROSS_PROFIT = 'GrossProfit'
    OPERATING_PROFIT_LOSS = 'OperatingIncomeLoss'
    EARNINGS_PER_SHARE_DILUTED = 'EarningsPerShareDiluted' 
    CURRENT_ASSETS = 'AssetsCurrent'
    NON_CURRENT_ASSETS = 'AssetsNoncurrent'
    CURRENT_LIABILITIES = 'LiabilitiesCurrent'
    NON_CURRENT_LIABILITES = 'LiabilitiesNoncurrent'
    DIVIDENDS = 'CommonStockDividendsPerShareDeclared' 
    CASH_GENERATED_BY_OPERATING_ACTIVITIES = 'NetCashProvidedByUsedInOperatingActivities'
    COST_OF_SALES = 'CostOfGoodsAndServicesSold' 
    CURRENT_ASSETS_INVENTORIES = 'InventoryNet'
    CURRENT_ASSETS_DEBTORS = 'AccountsReceivableNetCurrent'
    CURRENT_ASSETS_CASH = 'CashAndCashEquivalentsAtCarryingValue'
    CURRENT_ASSETS_OTHERS  = 'OtherAssetsCurrent'
    CURRENT_LIABILTIES_LOANS = 'LongTermDebtCurrent'
    CURRENT_LIABILITIES_CREDITORS = 'AccountsPayableCurrent' 
    CURRENT_LIABILITIES_OTHERS = 'OtherLiabilitiesCurrent'
    NON_CURRENT_LOANS = 'LongTermDebtNoncurrent'
In [105]:
data = pd.pivot_table(df, values = 'value', index = ['fact', 'stock_id'], columns = ['year'])
data
Out[105]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
AccountsPayableCurrent AAPL 30196000000.0 35490000000.0 37294000000.0 49049000000.0 55888000000.0 46236000000.0 42296000000.0 54763000000.0 64115000000.0
NVDA 293223000.0 296000000.0 485000000.0 596000000.0 511000000.0 687000000.0 1201000000.0 1783000000.0 1193000000.0
AccountsReceivableNetCurrent AAPL 17460000000.0 16849000000.0 15754000000.0 17874000000.0 23186000000.0 22926000000.0 16120000000.0 26278000000.0 28184000000.0
NVDA 473637000.0 505000000.0 826000000.0 1265000000.0 1424000000.0 1657000000.0 2429000000.0 4650000000.0 3827000000.0
AssetsCurrent AAPL 68531000000.0 89378000000.0 106869000000.0 128645000000.0 131339000000.0 162819000000.0 143713000000.0 134836000000.0 135405000000.0
NVDA 5713297000.0 6053000000.0 8536000000.0 9255000000.0 10557000000.0 13690000000.0 16055000000.0 28829000000.0 23073000000.0
AssetsNoncurrent AAPL 163308000000.0 201101000000.0 214817000000.0 246674000000.0 234386000000.0 175697000000.0 180175000000.0 216166000000.0 217350000000.0
NVDA 1488000000.0 1317000000.0 1305000000.0 1986000000.0 2735000000.0 3625000000.0 12736000000.0 15358000000.0 18109000000.0
CashAndCashEquivalentsAtCarryingValue AAPL 13844000000.0 21120000000.0 20484000000.0 20289000000.0 25913000000.0 48844000000.0 38016000000.0 34940000000.0 23646000000.0
NVDA 496654000.0 596000000.0 1766000000.0 4002000000.0 782000000.0 10896000000.0 847000000.0 1990000000.0 3389000000.0
CommonStockDividendsPerShareDeclared AAPL 1.82 1.98 2.18 2.4 2.72 3.0 0.795 0.85 0.9
NVDA 0.34 0.115 NaN NaN NaN NaN NaN NaN 0.16
CostOfGoodsAndServicesSold AAPL 112258000000.0 140089000000.0 131376000000.0 141048000000.0 163756000000.0 161782000000.0 169559000000.0 212981000000.0 223546000000.0
NVDA 2082030000.0 2199000000.0 2847000000.0 3892000000.0 4545000000.0 4150000000.0 6279000000.0 9439000000.0 11618000000.0
EarningsPerShareDiluted AAPL 6.45 9.22 8.31 9.21 11.91 11.89 3.28 5.61 6.11
NVDA 1.12 1.08 2.57 4.82 6.63 4.52 6.9 3.85 1.74
GrossProfit AAPL 70537000000.0 93626000000.0 84263000000.0 88186000000.0 101839000000.0 98392000000.0 104956000000.0 152836000000.0 170782000000.0
NVDA 2599477000.0 2811000000.0 4063000000.0 5822000000.0 7171000000.0 6768000000.0 10396000000.0 17475000000.0 15356000000.0
InventoryNet AAPL 2111000000.0 2349000000.0 2132000000.0 4855000000.0 3956000000.0 4106000000.0 4061000000.0 6580000000.0 4946000000.0
NVDA 482893000.0 418000000.0 794000000.0 796000000.0 1575000000.0 979000000.0 1826000000.0 2605000000.0 5159000000.0
LiabilitiesCurrent AAPL 63448000000.0 80610000000.0 79006000000.0 100814000000.0 116866000000.0 105718000000.0 105392000000.0 125481000000.0 153982000000.0
NVDA 896030000.0 2351000000.0 1788000000.0 1153000000.0 1329000000.0 1784000000.0 3925000000.0 4335000000.0 6563000000.0
LiabilitiesNoncurrent AAPL 56844000000.0 90514000000.0 114431000000.0 140458000000.0 141712000000.0 142310000000.0 153157000000.0 162431000000.0 148101000000.0
NVDA 1887000000.0 463000000.0 2260000000.0 2617000000.0 2621000000.0 3327000000.0 7973000000.0 13240000000.0 12518000000.0
LongTermDebtCurrent AAPL 0.0 2500000000.0 3500000000.0 6496000000.0 8784000000.0 10260000000.0 8773000000.0 9613000000.0 11128000000.0
NVDA 0.0 1413000000.0 796000000.0 15000000.0 0.0 0.0 999000000.0 0.0 1250000000.0
LongTermDebtNoncurrent AAPL 28987000000.0 53463000000.0 75427000000.0 97207000000.0 93735000000.0 91807000000.0 98667000000.0 109106000000.0 98959000000.0
NVDA 1384342000.0 0.0 1983000000.0 1985000000.0 1988000000.0 5964000000.0 5964000000.0 10946000000.0 9703000000.0
NetCashProvidedByUsedInOperatingActivities AAPL 59713000000.0 81266000000.0 65824000000.0 63598000000.0 77434000000.0 69391000000.0 80674000000.0 104038000000.0 122151000000.0
NVDA 905656000.0 1175000000.0 1672000000.0 3502000000.0 3743000000.0 4761000000.0 5822000000.0 9108000000.0 5641000000.0
OperatingIncomeLoss AAPL 52503000000.0 71230000000.0 60024000000.0 61344000000.0 70898000000.0 63930000000.0 66288000000.0 108949000000.0 119437000000.0
NVDA 758989000.0 747000000.0 1934000000.0 3210000000.0 3804000000.0 2846000000.0 4532000000.0 10041000000.0 4224000000.0
OtherAssetsCurrent AAPL 9806000000.0 9539000000.0 8283000000.0 13936000000.0 12087000000.0 12352000000.0 11264000000.0 14111000000.0 21223000000.0
NVDA 70174000.0 93000000.0 118000000.0 86000000.0 136000000.0 157000000.0 239000000.0 366000000.0 791000000.0
OtherLiabilitiesCurrent AAPL 0.0 0.0 0.0 0.0 32687000000.0 37720000000.0 42684000000.0 47493000000.0 60845000000.0
NVDA 602807000.0 642000000.0 507000000.0 542000000.0 818000000.0 1097000000.0 1725000000.0 2552000000.0 4120000000.0
RevenueFromContractWithCustomerExcludingAssessedTax AAPL 182795000000.0 233715000000.0 215639000000.0 229234000000.0 265595000000.0 260174000000.0 274515000000.0 365817000000.0 394328000000.0
NVDA 4682000000.0 5010000000.0 6910000000.0 9714000000.0 11716000000.0 10918000000.0 16675000000.0 26914000000.0 26974000000.0
In [106]:
def aggregate_values(fact, stock_id):
    return data.loc[fact, stock_id] / 1000000

rename_indexes = {
    Facts.TOTAL_NET_SALES.value : 'Total Net Sales',
    Facts.GROSS_PROFIT.value : 'Gross Profit',
    Facts.OPERATING_PROFIT_LOSS.value : 'Operating Profit',
    Facts.EARNINGS_PER_SHARE_DILUTED.value : 'Earnings Per Share',
    Facts.CURRENT_ASSETS.value : 'Current Assets',
    Facts.NON_CURRENT_ASSETS.value : 'Non-current Assets',
    Facts.CURRENT_LIABILITIES.value : 'Current Liabilities',
    Facts.NON_CURRENT_LIABILITES.value : 'Non-current Liabilities',
    Facts.DIVIDENDS.value : 'Dividends' ,
    Facts.CASH_GENERATED_BY_OPERATING_ACTIVITIES.value : 'Net Cash Flow from Operating Activities',
    Facts.COST_OF_SALES.value : 'Cost of Sales',
    Facts.CURRENT_ASSETS_INVENTORIES.value : 'Inventories',
    Facts.CURRENT_ASSETS_DEBTORS.value : 'Trade Debtors',
    Facts.CURRENT_ASSETS_CASH.value : 'Cash and Cash Equivalents',
    Facts.CURRENT_ASSETS_OTHERS.value  : 'Other Current Assets',
    Facts.CURRENT_LIABILTIES_LOANS.value : 'Current Long Term Debt',
    Facts.CURRENT_LIABILITIES_CREDITORS.value : 'Trade Creditors',
    Facts.CURRENT_LIABILITIES_OTHERS.value : 'Other Current Liabilities',
    Facts.NON_CURRENT_LOANS.value : 'Non-current Long Term Debt'
}

stock_ids = ['AAPL', 'NVDA']
data = data.rename(index = rename_indexes)
for stock_id in stock_ids:
    for fact in list(Facts):
        if fact.value == Facts.DIVIDENDS.value or fact.value == Facts.EARNINGS_PER_SHARE_DILUTED.value:
            continue
        data.loc[rename_indexes[fact.value], stock_id]= data.loc[rename_indexes[fact.value], stock_id] / 1000000
        
data
Out[106]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Trade Creditors AAPL 30196.0 35490.0 37294.0 49049.0 55888.0 46236.0 42296.0 54763.0 64115.0
NVDA 293.223 296.0 485.0 596.0 511.0 687.0 1201.0 1783.0 1193.0
Trade Debtors AAPL 17460.0 16849.0 15754.0 17874.0 23186.0 22926.0 16120.0 26278.0 28184.0
NVDA 473.637 505.0 826.0 1265.0 1424.0 1657.0 2429.0 4650.0 3827.0
Current Assets AAPL 68531.0 89378.0 106869.0 128645.0 131339.0 162819.0 143713.0 134836.0 135405.0
NVDA 5713.297 6053.0 8536.0 9255.0 10557.0 13690.0 16055.0 28829.0 23073.0
Non-current Assets AAPL 163308.0 201101.0 214817.0 246674.0 234386.0 175697.0 180175.0 216166.0 217350.0
NVDA 1488.0 1317.0 1305.0 1986.0 2735.0 3625.0 12736.0 15358.0 18109.0
Cash and Cash Equivalents AAPL 13844.0 21120.0 20484.0 20289.0 25913.0 48844.0 38016.0 34940.0 23646.0
NVDA 496.654 596.0 1766.0 4002.0 782.0 10896.0 847.0 1990.0 3389.0
Dividends AAPL 1.82 1.98 2.18 2.4 2.72 3.0 0.795 0.85 0.9
NVDA 0.34 0.115 NaN NaN NaN NaN NaN NaN 0.16
Cost of Sales AAPL 112258.0 140089.0 131376.0 141048.0 163756.0 161782.0 169559.0 212981.0 223546.0
NVDA 2082.03 2199.0 2847.0 3892.0 4545.0 4150.0 6279.0 9439.0 11618.0
Earnings Per Share AAPL 6.45 9.22 8.31 9.21 11.91 11.89 3.28 5.61 6.11
NVDA 1.12 1.08 2.57 4.82 6.63 4.52 6.9 3.85 1.74
Gross Profit AAPL 70537.0 93626.0 84263.0 88186.0 101839.0 98392.0 104956.0 152836.0 170782.0
NVDA 2599.477 2811.0 4063.0 5822.0 7171.0 6768.0 10396.0 17475.0 15356.0
Inventories AAPL 2111.0 2349.0 2132.0 4855.0 3956.0 4106.0 4061.0 6580.0 4946.0
NVDA 482.893 418.0 794.0 796.0 1575.0 979.0 1826.0 2605.0 5159.0
Current Liabilities AAPL 63448.0 80610.0 79006.0 100814.0 116866.0 105718.0 105392.0 125481.0 153982.0
NVDA 896.03 2351.0 1788.0 1153.0 1329.0 1784.0 3925.0 4335.0 6563.0
Non-current Liabilities AAPL 56844.0 90514.0 114431.0 140458.0 141712.0 142310.0 153157.0 162431.0 148101.0
NVDA 1887.0 463.0 2260.0 2617.0 2621.0 3327.0 7973.0 13240.0 12518.0
Current Long Term Debt AAPL 0.0 2500.0 3500.0 6496.0 8784.0 10260.0 8773.0 9613.0 11128.0
NVDA 0.0 1413.0 796.0 15.0 0.0 0.0 999.0 0.0 1250.0
Non-current Long Term Debt AAPL 28987.0 53463.0 75427.0 97207.0 93735.0 91807.0 98667.0 109106.0 98959.0
NVDA 1384.342 0.0 1983.0 1985.0 1988.0 5964.0 5964.0 10946.0 9703.0
Net Cash Flow from Operating Activities AAPL 59713.0 81266.0 65824.0 63598.0 77434.0 69391.0 80674.0 104038.0 122151.0
NVDA 905.656 1175.0 1672.0 3502.0 3743.0 4761.0 5822.0 9108.0 5641.0
Operating Profit AAPL 52503.0 71230.0 60024.0 61344.0 70898.0 63930.0 66288.0 108949.0 119437.0
NVDA 758.989 747.0 1934.0 3210.0 3804.0 2846.0 4532.0 10041.0 4224.0
Other Current Assets AAPL 9806.0 9539.0 8283.0 13936.0 12087.0 12352.0 11264.0 14111.0 21223.0
NVDA 70.174 93.0 118.0 86.0 136.0 157.0 239.0 366.0 791.0
Other Current Liabilities AAPL 0.0 0.0 0.0 0.0 32687.0 37720.0 42684.0 47493.0 60845.0
NVDA 602.807 642.0 507.0 542.0 818.0 1097.0 1725.0 2552.0 4120.0
Total Net Sales AAPL 182795.0 233715.0 215639.0 229234.0 265595.0 260174.0 274515.0 365817.0 394328.0
NVDA 4682.0 5010.0 6910.0 9714.0 11716.0 10918.0 16675.0 26914.0 26974.0

3.2 Calculation of Metrics¶

3.2.1 10-year Summary Overview¶

In [107]:
def create_graph(metrics, table, ylabel = 'Percentage %'):
    fig, axes = plt.subplots(nrows=len(metrics), ncols=1, figsize=(10, 5*len(metrics)), sharex=True)
    for i, metric in enumerate(metrics):
        ax = axes[i] if len(metrics) > 1 else axes
        for stock_id in stock_ids:
            ax.plot(table.columns, table.loc[metric, stock_id], label = stock_id)

            ax.set_title(metric)
            ax.set_xlabel('Years')
            ax.set_ylabel(ylabel)
            ax.legend()

Net assets = Non-current assets + current assets - current liabilities

Return on net assets (RONA): RONA measures ROI by expressing the operating profit before interest charges and taxation as a percentage of the company's net assets. RONA is a good measure of how effective the firm manages its net assets. It is not affected by the way in which the business is funded, which makes it a good ratio for comparison with prior years and with other businesses.

In [108]:
# Missing - Profit attributable to equity shareholders, closing share price, earnings per share, dividend per share, operating cash flow per share
fact = ['Sales Revenue', 'Operating Profit', 'Net Assets', 'Return on Net Assets %', 'Current Liabilities Ratio %']
stock_ids = ['AAPL', 'NVDA']
years = data.columns

multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
summary_table = pd.DataFrame(index=multi_index, columns=years)
for x in years:
    for stock_id in stock_ids:
        summary_table.loc[('Sales Revenue', stock_id), x] = data.loc[('Total Net Sales', stock_id), x]
        summary_table.loc[('Operating Profit', stock_id), x] = data.loc[('Operating Profit', stock_id), x]
        summary_table.loc[('Net Assets', stock_id), x] = data.loc[('Non-current Assets', stock_id), x] + data.loc[('Current Assets', stock_id), x] - data.loc[('Current Liabilities', stock_id), x]
        summary_table.loc[('Return on Net Assets %', stock_id), x] = (summary_table.loc[('Operating Profit', stock_id), x] / summary_table.loc[('Net Assets', stock_id), x]) * 100
        summary_table.loc[('Current Liabilities Ratio %', stock_id), x] = (data.loc[('Current Liabilities', stock_id), x] / summary_table.loc[('Net Assets', stock_id), x]) * 100

summary_table
Out[108]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Sales Revenue AAPL 182795.0 233715.0 215639.0 229234.0 265595.0 260174.0 274515.0 365817.0 394328.0
NVDA 4682.0 5010.0 6910.0 9714.0 11716.0 10918.0 16675.0 26914.0 26974.0
Operating Profit AAPL 52503.0 71230.0 60024.0 61344.0 70898.0 63930.0 66288.0 108949.0 119437.0
NVDA 758.989 747.0 1934.0 3210.0 3804.0 2846.0 4532.0 10041.0 4224.0
Net Assets AAPL 168391.0 209869.0 242680.0 274505.0 248859.0 232798.0 218496.0 225521.0 198773.0
NVDA 6305.267 5019.0 8053.0 10088.0 11963.0 15531.0 24866.0 39852.0 34619.0
Return on Net Assets % AAPL 31.17922 33.94022 24.733806 22.347134 28.489225 27.461576 30.338313 48.309913 60.087135
NVDA 12.037381 14.883443 24.015895 31.819984 31.798044 18.324641 18.22569 25.195724 12.201392
Current Liabilities Ratio % AAPL 37.678973 38.409675 32.555629 36.725743 46.960729 45.411902 48.235208 55.640495 77.466255
NVDA 14.210818 46.842 22.202906 11.429421 11.109254 11.486704 15.784605 10.877748 18.957798
In [109]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Return on Net Assets %', 'Current Liabilities Ratio %']

ax1.plot(summary_table.columns, summary_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(summary_table.columns, summary_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

ax2 = ax1.twinx()
ax2.plot(summary_table.columns, summary_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax2.plot(summary_table.columns, summary_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')

ax2.set_ylabel(metrics[1], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')

plt.title('Return on Net Assets % and Current Liabilities Ratio % over Years')
plt.grid(True)
plt.show()

Observations

  • For AAPL, a high 'Return on Net Assets' corresponds to a high 'Current Liabilities Ratio,' indicating a direct correlation between operating profit and current liabilities.
  • In contrast, for NVDA, a high 'Return on Net Assets' aligns with a low 'Current Liabilities Ratio,' showcasing an inverse correlation between operating profit and current liabilities.

Analysis

  • For AAPL, the direct correlation between a high 'Return on Net Assets' and a high 'Current Liabilities Ratio' may indicate a strategy where higher returns on assets are achieved alongside increased reliance on short-term liabilities to support operations.
  • Conversely, in the case of NVDA, the inverse correlation between a high 'Return on Net Assets' and a low 'Current Liabilities Ratio' indicates efficient utilization of assets to generate strong returns while maintaining a relatively lower reliance on short-term liabilities. This could indicate a conservative approach in managing liabilities while maximizing returns on assets.

  • NVDA wins

3.2.2 Income Statement Analysis¶

In [111]:
# Missing - Profit attributable to equity shareholders
fact = ['Sales Revenue', 'Gross Profit', 'Operating Profit', 'Gross Profit %', 'Operating Profit %', 'Sales Revenue % change', 'Gross Profit % change', 'Operating Profit % change', 'Gross vs Operating Ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
income_statement_table = pd.DataFrame(index=multi_index, columns=years)

for x in years:
    for stock_id in stock_ids:
        income_statement_table.loc[('Sales Revenue', stock_id), x] = data.loc[('Total Net Sales', stock_id), x]
        income_statement_table.loc[('Gross Profit', stock_id), x] = data.loc[('Gross Profit', stock_id), x]
        income_statement_table.loc[('Operating Profit', stock_id), x] = data.loc[('Operating Profit', stock_id), x]
        income_statement_table.loc[('Gross Profit %', stock_id), x] = (income_statement_table.loc[('Gross Profit', stock_id), x] / income_statement_table.loc[('Sales Revenue', stock_id), x]) * 100
        income_statement_table.loc[('Operating Profit %', stock_id), x] = (income_statement_table.loc[('Operating Profit', stock_id), x] / income_statement_table.loc[('Sales Revenue', stock_id), x]) * 100
        income_statement_table.loc[('Gross vs Operating Ratio', stock_id), x] = income_statement_table.loc[('Gross Profit', stock_id), x] / income_statement_table.loc[('Operating Profit', stock_id), x]
        
for stock_id in stock_ids:
    income_statement_table.loc[('Sales Revenue % change', stock_id)] = income_statement_table.loc[('Sales Revenue', stock_id)].pct_change(periods = 1)
    income_statement_table.loc[('Gross Profit % change', stock_id)] = income_statement_table.loc[('Gross Profit', stock_id)].pct_change(periods = 1)
    income_statement_table.loc[('Operating Profit % change', stock_id)] = income_statement_table.loc[('Operating Profit', stock_id)].pct_change(periods = 1)

income_statement_table
Out[111]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Sales Revenue AAPL 182795.0 233715.0 215639.0 229234.0 265595.0 260174.0 274515.0 365817.0 394328.0
NVDA 4682.0 5010.0 6910.0 9714.0 11716.0 10918.0 16675.0 26914.0 26974.0
Gross Profit AAPL 70537.0 93626.0 84263.0 88186.0 101839.0 98392.0 104956.0 152836.0 170782.0
NVDA 2599.477 2811.0 4063.0 5822.0 7171.0 6768.0 10396.0 17475.0 15356.0
Operating Profit AAPL 52503.0 71230.0 60024.0 61344.0 70898.0 63930.0 66288.0 108949.0 119437.0
NVDA 758.989 747.0 1934.0 3210.0 3804.0 2846.0 4532.0 10041.0 4224.0
Gross Profit % AAPL 38.588036 40.059902 39.075956 38.46986 38.343719 37.817768 38.233248 41.77936 43.309631
NVDA 55.520654 56.107784 58.798842 59.934116 61.206897 61.989375 62.344828 64.929033 56.928894
Operating Profit % AAPL 28.722339 30.477291 27.83541 26.760428 26.694027 24.572017 24.147314 29.782378 30.288744
NVDA 16.210786 14.91018 27.988423 33.04509 32.468419 26.067045 27.178411 37.307721 15.659524
Sales Revenue % change AAPL NaN 0.278563 -0.077342 0.063045 0.15862 -0.020411 0.055121 0.332594 0.077938
NVDA NaN 0.070056 0.379242 0.405789 0.206094 -0.068112 0.527294 0.614033 0.002229
Gross Profit % change AAPL NaN 0.327332 -0.100004 0.046557 0.15482 -0.033848 0.066713 0.456191 0.11742
NVDA NaN 0.081371 0.445393 0.432931 0.231707 -0.056199 0.536052 0.680935 -0.121259
Operating Profit % change AAPL NaN 0.356684 -0.157321 0.021991 0.155745 -0.098282 0.036884 0.64357 0.096265
NVDA NaN -0.015796 1.589023 0.659772 0.185047 -0.25184 0.59241 1.215578 -0.579325
Gross vs Operating Ratio AAPL 1.343485 1.314418 1.403822 1.437565 1.436416 1.539058 1.583333 1.402822 1.429892
NVDA 3.424921 3.763052 2.100827 1.813707 1.885121 2.378074 2.29391 1.740365 3.635417
In [125]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Gross Profit %', 'Operating Profit %']

ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')

ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')


plt.title('Gross Profit % and Operating Profit %')
plt.grid(True)
plt.show()
In [131]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Gross vs Operating Ratio']

ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(income_statement_table.columns, income_statement_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

plt.title('Gross vs Operating Ratio')
plt.grid(True)
plt.show()

Observation

  • Over the years, NVDA consistently demonstrates higher Gross Profit figures compared to AAPL.
  • NVDA demonstrates higher Operating Profit figures compared to AAPL for most years.
  • There is a wider gap between the Gross Profit and Operating Profit for NVDA as compared to AAPL.

Analysis

  • A substantial gap between gross profit and operating profit often indicates that a company is incurring significant operating expenses, such as administrative, selling, or research and development costs, after accounting for the cost of goods sold (COGS). This gap reflects the impact of these expenses on the overall profitability of the company, highlighting the difference between the revenue generated from sales and the earnings after considering both COGS and operational expenses.

  • (Further Analysis: Need to find out what are the operating expenses of NVDA and AAPL)

3.2.3 Balance Sheet Analysis¶

To Note for NVDA:

  • Other Current Liabilities = Accrued + Other Current Liabilities
  • Other Current Assets = Prepaid expenses + Other Current Assets
In [114]:
# Net Working Capital = Current Assets - Current Liabilities
# Net Assets = Non-current Assets + Current Assets - Current Liabilities
fact = ['Net Assets', 'Non-current Assets', 'Current Assets', 'Current Liabilities excluding loans', 'Net Working Capital', 'Debt', 'Non-current Assets %', 'Current Assets %', 'Current Liabilities %', 'Net Working Capital %', 'Debt %', 'Net Working Capital vs Debt Ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
balance_sheet_table = pd.DataFrame(index=multi_index, columns=years)

for x in years:
    for stock_id in stock_ids:
        balance_sheet_table.loc[('Net Assets', stock_id), x] = summary_table.loc[('Net Assets', stock_id), x]
        balance_sheet_table.loc[('Non-current Assets', stock_id), x] = data.loc[('Non-current Assets', stock_id), x]
        balance_sheet_table.loc[('Current Assets', stock_id), x] = data.loc[('Current Assets', stock_id), x]
        balance_sheet_table.loc[('Current Liabilities excluding loans', stock_id), x] = data.loc[('Trade Creditors', stock_id), x] + data.loc[('Other Current Liabilities', stock_id), x]
        balance_sheet_table.loc[('Net Working Capital', stock_id), x] = data.loc[('Current Assets', stock_id), x] - (data.loc[('Trade Creditors', stock_id), x] + data.loc[('Other Current Liabilities', stock_id), x])
        balance_sheet_table.loc[('Debt', stock_id), x] = data.loc[('Current Long Term Debt', stock_id), x] + data.loc[('Non-current Long Term Debt', stock_id), x]
        balance_sheet_table.loc[('Non-current Assets %', stock_id), x] = (data.loc[('Non-current Assets', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
        balance_sheet_table.loc[('Current Assets %', stock_id), x] = (data.loc[('Current Assets', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
        balance_sheet_table.loc[('Current Liabilities %', stock_id), x] = (data.loc[('Current Liabilities', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
        balance_sheet_table.loc[('Net Working Capital %', stock_id), x] = (balance_sheet_table.loc[('Net Working Capital', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
        balance_sheet_table.loc[('Debt %', stock_id), x] = (balance_sheet_table.loc[('Debt', stock_id), x] / balance_sheet_table.loc[('Net Assets', stock_id), x]) * 100
        balance_sheet_table.loc[('Net Working Capital vs Debt Ratio', stock_id), x] = (balance_sheet_table.loc[('Net Working Capital', stock_id), x] / balance_sheet_table.loc[('Debt', stock_id), x])

balance_sheet_table
Out[114]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Net Assets AAPL 168391.0 209869.0 242680.0 274505.0 248859.0 232798.0 218496.0 225521.0 198773.0
NVDA 6305.267 5019.0 8053.0 10088.0 11963.0 15531.0 24866.0 39852.0 34619.0
Non-current Assets AAPL 163308.0 201101.0 214817.0 246674.0 234386.0 175697.0 180175.0 216166.0 217350.0
NVDA 1488.0 1317.0 1305.0 1986.0 2735.0 3625.0 12736.0 15358.0 18109.0
Current Assets AAPL 68531.0 89378.0 106869.0 128645.0 131339.0 162819.0 143713.0 134836.0 135405.0
NVDA 5713.297 6053.0 8536.0 9255.0 10557.0 13690.0 16055.0 28829.0 23073.0
Current Liabilities excluding loans AAPL 30196.0 35490.0 37294.0 49049.0 88575.0 83956.0 84980.0 102256.0 124960.0
NVDA 896.03 938.0 992.0 1138.0 1329.0 1784.0 2926.0 4335.0 5313.0
Net Working Capital AAPL 38335.0 53888.0 69575.0 79596.0 42764.0 78863.0 58733.0 32580.0 10445.0
NVDA 4817.267 5115.0 7544.0 8117.0 9228.0 11906.0 13129.0 24494.0 17760.0
Debt AAPL 28987.0 55963.0 78927.0 103703.0 102519.0 102067.0 107440.0 118719.0 110087.0
NVDA 1384.342 1413.0 2779.0 2000.0 1988.0 5964.0 6963.0 10946.0 10953.0
Non-current Assets % AAPL 96.98143 95.822156 88.518625 89.861387 94.184257 75.471868 82.461464 95.851828 109.345837
NVDA 23.599318 26.240287 16.205141 19.686757 22.862158 23.340416 51.218531 38.537589 52.309425
Current Assets % AAPL 40.697543 42.587519 44.037003 46.864356 52.776472 69.940034 65.773744 59.788667 68.120419
NVDA 90.6115 120.601713 105.997765 91.742665 88.247095 88.146288 64.566074 72.340159 66.648372
Current Liabilities % AAPL 37.678973 38.409675 32.555629 36.725743 46.960729 45.411902 48.235208 55.640495 77.466255
NVDA 14.210818 46.842 22.202906 11.429421 11.109254 11.486704 15.784605 10.877748 18.957798
Net Working Capital % AAPL 22.765468 25.67697 28.669441 28.996193 17.184028 33.87615 26.880584 14.446548 5.254738
NVDA 76.400682 101.912732 93.679374 80.461935 77.137842 76.659584 52.799003 61.462411 51.301309
Debt % AAPL 17.214103 26.665682 32.523076 37.778183 41.195617 43.84359 49.172525 52.642104 55.383276
NVDA 21.955327 28.153019 34.508879 19.825535 16.617905 38.400618 28.002091 27.466627 31.638696
Net Working Capital vs Debt Ratio AAPL 1.322489 0.962922 0.881511 0.767538 0.417132 0.772659 0.546659 0.27443 0.09488
NVDA 3.479824 3.619958 2.714646 4.0585 4.641851 1.996311 1.885538 2.237712 1.621474
In [132]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Net Working Capital %', 'Debt %']

ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')

ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')


plt.title('Net Working Capital % and Debt %')
plt.grid(True)
plt.show()
In [116]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Net Working Capital vs Debt Ratio']

# Plot Return on Net Assets %
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(balance_sheet_table.columns, balance_sheet_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

plt.title('Net Working Capital vs Debt Ratio')
plt.grid(True)
plt.show()

Observations

  • NVDA consistently having Net Working Capital sufficient to cover its debt while AAPL shows a scenario where debt surpasses Net Working Capital.
  • Over the years, NVDA consistently maintains a notably stronger Net Working Capital compared to Debt Ratio in contrast to AAPL.

Analysis

  • NVDA wins
  • (Further Analysis: Find out why Debt ratio is high)

3.2.4 Profitability Ratios¶

  • Profit Margin - Calculates the rate at which a business generates profit on its sales
  • Net Asset Turnover - Reflects the capital investment needed in an industry sector to be able to compete and generate sales revenue
In [117]:
fact = ['Return on Net Assets %', 'Profit Margin %', 'Net Asset Turnover']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
profitability_ratios_table = pd.DataFrame(index=multi_index, columns=years)

for x in years:
    for stock_id in stock_ids:
        profitability_ratios_table.loc[('Return on Net Assets %', stock_id), x] = summary_table.loc[('Return on Net Assets %', stock_id), x]
        profitability_ratios_table.loc[('Profit Margin %', stock_id), x] = (summary_table.loc[('Operating Profit', stock_id), x] / summary_table.loc[('Sales Revenue', stock_id), x]) * 100
        profitability_ratios_table.loc[('Net Asset Turnover', stock_id), x] = summary_table.loc[('Sales Revenue', stock_id), x] / summary_table.loc[('Net Assets', stock_id), x]

profitability_ratios_table
Out[117]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Return on Net Assets % AAPL 31.17922 33.94022 24.733806 22.347134 28.489225 27.461576 30.338313 48.309913 60.087135
NVDA 12.037381 14.883443 24.015895 31.819984 31.798044 18.324641 18.22569 25.195724 12.201392
Profit Margin % AAPL 28.722339 30.477291 27.83541 26.760428 26.694027 24.572017 24.147314 29.782378 30.288744
NVDA 16.210786 14.91018 27.988423 33.04509 32.468419 26.067045 27.178411 37.307721 15.659524
Net Asset Turnover AAPL 1.085539 1.113623 0.888573 0.835081 1.067251 1.117596 1.256385 1.622097 1.983811
NVDA 0.742554 0.998207 0.858065 0.962926 0.979353 0.702981 0.670594 0.675349 0.779168
In [139]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Return on Net Assets %']

ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

plt.title('Return on Net Assets')
plt.grid(True)
plt.show()

Observations

  • The lower Return on Net Assets for NVDA compared to AAPL implies that NVDA generates comparatively lower operating profit concerning its net assets when juxtaposed with AAPL.

Analysis

  • A lower Return on Net Assets for NVDA compared to AAPL could indicate potentially lower efficiency in utilizing its assets to generate operating profits compared to AAPL. This might suggest differences in operational effectiveness or resource allocation strategies between the two companies.
  • AAPL wins.
In [140]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Profit Margin %']

ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

plt.title('Profit Margin %')
plt.grid(True)
plt.show()

Observation:

  • NVDA has a higher profit margin than AAPL for most years

Analysis:

  • AAPL wins
In [137]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Net Asset Turnover']

ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(profitability_ratios_table.columns, profitability_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

plt.title('Net Asset Turnover')
plt.grid(True)
plt.show()

Observations

  • Over the years, AAPL demonstrates a consistent rise in Net Asset Turnover, surpassing NVDA and showcasing consistently higher figures.

Analysis

  • High asset turnover signifies a company's efficiency in utilizing its assets to generate sales or revenue. It indicates that the company effectively utilizes its assets to generate higher sales or revenue relative to the value of its assets. Essentially, a high asset turnover ratio implies that the company is efficiently generating more revenue per unit of assets.

3.2.5 Liquidity Ratios¶

  • Current Ratio - Measures the extent to which the cash, and other assets that can be converted back into cash within 12 months, cover the debts that will fall due for payment within 12 months.
  • Acid Test Ratio - Takes into account only those assets that can be quickly converted into cash
In [119]:
fact = ['Current ratio', 'Acid test ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
liquidity_ratios_table = pd.DataFrame(index=multi_index, columns=years)

for x in years:
    for stock_id in stock_ids:
        liquidity_ratios_table.loc[('Current ratio', stock_id), x] = data.loc[('Current Assets', stock_id), x] / data.loc[('Current Liabilities', stock_id), x]
        liquidity_ratios_table.loc[('Acid test ratio', stock_id), x] = (data.loc[('Trade Debtors', stock_id), x] + data.loc[('Cash and Cash Equivalents', stock_id), x]) / data.loc[('Current Liabilities', stock_id), x]

liquidity_ratios_table
Out[119]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Current ratio AAPL 1.080113 1.108771 1.352669 1.276063 1.123843 1.540126 1.363604 1.074553 0.879356
NVDA 6.376234 2.574649 4.774049 8.026886 7.943567 7.673767 4.090446 6.650288 3.515618
Acid test ratio AAPL 0.49338 0.471021 0.458674 0.378549 0.420131 0.678882 0.513663 0.487867 0.336598
NVDA 1.082878 0.468311 1.449664 4.568083 1.659895 7.036435 0.83465 1.531719 1.099497
In [120]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Current ratio', 'Acid test ratio']

ax1.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

ax2 = ax1.twinx()
ax2.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[1], 'AAPL'], label=f'AAPL - {metrics[1]}', linestyle='--', marker='x', color='#5DBB63')
ax2.plot(liquidity_ratios_table.columns, liquidity_ratios_table.loc[metrics[1], 'NVDA'], label=f'NVDA - {metrics[1]}', linestyle='--', marker='x', color='#F28500')

ax2.set_ylabel(metrics[1], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')

plt.title('Current ratio and Acid test ratio over Years')
plt.grid(True)
plt.show()

3.2.6 Cash Flow Ratios¶

  • Operating cash flow - Provides an indication of how easily the business can meet its short-term liabilities from the cash it has generated from its operations during the year
  • Cash flow margin ratio - A high ratio indicates that a company is efficient at converting its sales to cash
  • Cash flow to debt ratio - The ratio provides an indication of a company's ability to cover total debt from its annual operating cash flow
In [141]:
fact = ['Operating Cash Flow ratio', 'Cash Flow Margin ratio', 'Cash Flow to Debt ratio']
multi_index = pd.MultiIndex.from_product([fact, stock_ids], names=['fact', 'stock_id'])
cash_flow_ratio_table = pd.DataFrame(index=multi_index, columns=years)

for x in years:
    for stock_id in stock_ids:
        cash_flow_ratio_table.loc[('Operating Cash Flow ratio', stock_id), x] = data.loc[('Net Cash Flow from Operating Activities', stock_id), x] / data.loc[('Current Liabilities', stock_id), x]
        cash_flow_ratio_table.loc[('Cash Flow Margin ratio', stock_id), x] = (data.loc[('Net Cash Flow from Operating Activities', stock_id), x] / summary_table.loc[('Sales Revenue', stock_id), x]) * 100
        cash_flow_ratio_table.loc[('Cash Flow to Debt ratio', stock_id), x] = (data.loc[('Net Cash Flow from Operating Activities', stock_id), x] / (data.loc[('Current Liabilities', stock_id), x] + data.loc[('Non-current Liabilities', stock_id), x])) * 100
    
cash_flow_ratio_table
Out[141]:
year 2014 2015 2016 2017 2018 2019 2020 2021 2022
fact stock_id
Operating Cash Flow ratio AAPL 0.941133 1.008138 0.833152 0.630845 0.662588 0.656378 0.765466 0.829114 0.793281
NVDA 1.010743 0.499787 0.935123 3.037294 2.816403 2.668722 1.483312 2.101038 0.859515
Cash Flow Margin ratio AAPL 32.666648 34.77141 30.525091 27.743703 29.154916 26.670997 29.387829 28.439903 30.977004
NVDA 19.343358 23.453094 24.196816 36.05106 31.947764 43.606888 34.914543 33.841124 20.912731
Cash Flow to Debt ratio AAPL 49.640043 47.48954 34.02865 26.359462 29.94609 27.977083 31.202596 36.135347 40.436238
NVDA 32.542085 41.755508 41.304348 92.891247 94.759494 93.152025 48.932594 51.823613 29.56344
In [142]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Operating Cash Flow ratio']

ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax1.set_xlabel('Years')
ax1.set_ylabel(metrics[0], color='blue')
ax1.tick_params(axis='y', labelcolor='blue')
ax1.legend(loc='upper left')

plt.title('Operating Cash Flow ratio')
plt.grid(True)
plt.show()
In [144]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Cash Flow Margin ratio']

ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax2.set_ylabel(metrics[0], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')

plt.title('Cash Flow Margin ratio')
plt.grid(True)
plt.show()
In [145]:
fig, ax1 = plt.subplots(figsize=(10, 6))
metrics = ['Cash Flow to Debt ratio']

ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'AAPL'], label=f'AAPL - {metrics[0]}', linestyle='-', marker='o', color='#3CB043')
ax1.plot(cash_flow_ratio_table.columns, cash_flow_ratio_table.loc[metrics[0], 'NVDA'], label=f'NVDA - {metrics[0]}', linestyle='-', marker='o', color='#F04A00')

ax2.set_ylabel(metrics[0], color='red')
ax2.tick_params(axis='y', labelcolor='red')
ax2.legend(loc='upper right')

plt.title('Cash Flow to Debt ratio')
plt.grid(True)
plt.show()

Observations

  • NVDA tends to outperform AAPL across all five metrics, including the Operating Cash Flow Ratio, Cash Flow Margin Ratio, Cash Flow to Debt Ratio, Current Ratio, and Acid Test Ratio.

Analysis

  • AAPL's Net Cash Flow from operating activities indicates better coverage for current liabilities and debts compared to NVDA.
  • NVDA excels in converting its sales or revenue into cash, highlighting its operational efficiency in generating cash flow.
  • This is also further evident in the Current and Acid test ratio.
In [123]:
cnx.close()

4 Conclusion¶

4.1 Analysis on Financial Metrics¶

NVDA typically outperforms AAPL across multiple metrics over the years. However, a decline in NVDA's metrics from 2021 to 2022 raises questions about the simultaneous increase in cumulative return during that period.

Despite NVDA's declining metrics, such as profitability, the sustained rise in cumulative return implies ongoing investor confidence in NVDA's long-term potential. This trend could be influenced by the growing significance of AI, further enhancing NVDA's attractiveness in the market.

Additionally, NVDA maintains a higher profit margin than AAPL but exhibits lower net asset turnover and return on net assets. This suggests NVDA generates substantial profits relative to sales revenue but might underutilize its assets for generating sales. While profitability per sales unit remains healthy, there's potential to improve asset utilization for driving higher sales relative to its asset base.

4.2 Potential Improvements¶

Here are potential areas for project enhancement in the future:

  • Gain insights into macroeconomics, particularly its impact on global markets, to deepen comprehension of market dynamics.
  • Analyze shareholders’ equity, focusing on dividends and stock splits
  • Develop predictive modeling techniques, such as time series analysis, to forecast future stock movements and trends.
In [ ]: